create or replace function generate_node_state(today date = current_date) returns date as $$
	declare
	yesterday date := today - 1;
	begin
	insert into t_node_state (euid, time, is_on, status, energy, last_data_time, last_error_time)
	select euid, today, is_on, status, 0, last_data_time, last_error_time from t_node_state where time = yesterday;
	return today;
	end;
$$ LANGUAGE plpgsql;



create or replace function update_node_state(peuid bigint, pis_on boolean, pstatus integer, penergy integer, data_time timestamp) returns date as $$
declare
today date := current_date;
yesterday date := today - 1;
energy_init integer := 0;
energy_today integer := 0;
energy_yesterday integer := 0;
begin
	select a.init_energy into energy_init from t_node_state a where a.euid = peuid and time = today;
	if found then
		if penergy < energy_init then
			energy_today := penergy - energy_init;
		else
			energy_today := penergy;
		end if;
		update t_node_state  set energy=energy_today where t_node_state.euid = peuid and time = today;
	else
		select a.init_energy, a.energy into energy_init, energy_yesterday  from t_node_state a where a.euid = peuid and time = yesterday;
		if found then
			insert into t_node_state (euid, time, is_on, status, energy, init_energy, last_data_time ) 
			values(peuid, today, pis_on, pstatus, penergy, energy_init + energy_yesterday, data_time);
		else
			insert into t_node_state (euid, time, is_on, status, energy, init_energy, last_data_time ) 
			values(peuid, today, pis_on, pstatus, penergy, 0, data_time);
		end if;

	end if;
	return today;
end;
$$ LANGUAGE plpgsql;

create or replace function append_hydrant_data(euid_ text, pressure_ integer, trend_ integer, signal_ smallint, sim_id_ text, voltage_ integer) returns integer as $$
declare
pk_of_euid integer;
prev_pressure integer := 0;
ts timestamp;
begin
	select id into pk_of_euid from t_node_info2 where euid = euid_;
	if not found then
		--insert into t_node_info2(euid) values(euid_);
		--select id into pk_of_euid from t_node_info2 where euid = euid_;
		--if not found then
			return 0;
		--end if;
	end if;

	select max(time) into ts from t_hydrant_data where euid = pk_of_euid;
	if found then
		select pressure into prev_pressure from t_hydrant_data where euid = pk_of_euid and time = ts;
	end if;

	insert into t_hydrant_data (euid, time, pressure, pressure_trend, signal, sim_id, voltage) values(pk_of_euid, localtimestamp, pressure_, trend_, signal_, sim_id_, voltage_);
	update t_node_info2 set sim_id = sim_id_ where euid = euid_;

	return prev_pressure;
end;
$$ LANGUAGE plpgsql;

grant  all on function append_hydrant_data(euid_ text, pressure_ integer, trend_ integer, signal_ smallint, sim_id_ text, voltage_ integer) to csapp;


create or replace function append_well_lip_data(euid_ text, signal_ smallint, sim_id_ text, cover_status_ smallint, submerged_ smallint) returns void as $$
declare
pk_of_euid integer;
begin
	select id into pk_of_euid from t_node_info2 where euid = euid_;
	if found then
		insert into t_magnetic_data (euid, time, signal, sim_id, cover_move, submerged) values(pk_of_euid, localtimestamp, signal_, sim_id_, cover_status_, submerged_);
		update t_node_info2 set sim_id = sim_id_ where euid = euid_;
	end if;
end;
$$ LANGUAGE plpgsql;

grant all on function append_well_lip_data(euid_ text, signal_ smallint, sim_id_ text, cover_status_ smallint, submerged_ smallint) to csapp;

create or replace function append_smokemonitor_data(euid_ text, signal_ smallint, sim_id_ text, event_id_ integer, event_type_ smallint, utc_time_ text) returns integer as $$
declare
pk_of_euid integer;
begin
	select id into pk_of_euid from t_node_info2 where euid = euid_;
	if found then
		insert into t_smokemonitor_data (euid, time, signal, simid, event_id, event_type, utc_time) 
		values(pk_of_euid, localtimestamp, signal_, sim_id_, event_id_, event_type_, utc_time_);
		if sim_id_ is not null then 
			update t_node_info2 set sim_id = sim_id_ where euid = euid_;
		end if;
		return 1;
	end if;
	return 0;
end;
$$ LANGUAGE plpgsql;

grant all on function append_smokemonitor_data(euid_ text, signal_ smallint, sim_id_ text, event_id_ integer, event_type_ smallint, utc_time_ text) to csapp;

create or replace function update_simid(euid_ text, simid_ text) returns void as $$
begin
	update t_node_info2 set sim_id = simid_ where euid = euid_;
end;
$$ LANGUAGE plpgsql;
grant all on function update_simid(euid_ text, simid_ text) to csapp;

create or replace function append_environment(euid_ text, signal_ smallint, sim_id_ text, adjust_second integer, temperature_ integer, humidity_ integer, 
	pm25_ integer, pm10_ integer, pm1_ integer, windspeed_ integer, winddirection_ integer, noise_ integer) returns void as $$
declare
pk_of_euid integer;
begin
	select id into pk_of_euid from t_node_info2 where euid = euid_;
	if found then
		insert into t_environment_data (euid, time, signal, sim_id, temperature, humidity, pm25, pm10, pm1, windspeed, winddirection, noise) 
		values(pk_of_euid, localtimestamp - adjust_second * interval '1 second', signal_, sim_id_, temperature_, humidity_, pm25_, pm10_, pm1_, windspeed_, winddirection_, noise_);
	end if;
end;
$$ LANGUAGE plpgsql;
grant all on function append_environment(text, smallint, text, integer, integer, integer, integer, integer, integer, integer, integer, integer) to csapp;

create table t_smokemonitor_reg(
	id bigserial primary key,
	euid varchar(64),
	time timestamp, 
	simid varchar(64), 
	type_name varchar(64), 
	address varchar(128), 
	position varchar(128), 
	lat double precision, 
	lng double precision
);
grant all on table  t_smokemonitor_reg to csapp;
grant all on t_smokemonitor_reg_id_seq to csapp;
create index idx_smokemonitor_reg_time on t_smokemonitor_reg(time);
create index idx_smokemonitor_reg_euid on t_smokemonitor_reg(euid);


create or replace function reg_smokemonitor(euid_ text, sim_id_ text, type_name_ text, address_ text, position_ text, lat_ double precision, lng_ double precision) returns integer as $$
declare
pk_of_euid integer;
begin
	insert into t_smokemonitor_reg (euid, time, simid, type_name, address, position, lat, lng) 
	values(euid_, localtimestamp, sim_id_, type_name_, address_, position_, lat_, lng_);

	select id into pk_of_euid from t_node_info2 where euid = euid_;
	if found then
		update t_node_info2 set sim_id = sim_id_ where id = pk_of_euid;
	end if;
	return 1;
end;
$$ LANGUAGE plpgsql;
grant all on function reg_smokemonitor(euid_ text, sim_id_ text, type_name_ text, address_ text, position_ text, lat_ double precision, lng_ double precision) to csapp;




create or replace function create_organization(name_ text, description_ text, status_ integer, prefix_ text) returns integer as $$
declare
orgid_ integer;
begin
	insert into t_organization (name, description, status, prefix) values(name_, description_, status_, prefix_) returning id into orgid_;
	insert into t_org_modules (orgid, moduleid) values(orgid_, 1);	-- api/v2/account
	insert into t_org_modules (orgid, moduleid) values(orgid_, 9);	-- api/v2/accadm
	return orgid_;
end;
$$ LANGUAGE plpgsql;
grant all on function create_organization(name_ text, description_ text, status_ integer, prefix_ text) to csapp;

create or replace function create_user(account_ text, password_ text, name_ text, description_ text, orgid_ integer) returns integer as $$
declare
userid_ integer;
begin
	insert into t_user2(account, password, name, orgid) values(account_, encode(digest(concat(password_, '{', account_, '}'), 'sha1'), 'hex'), name_, orgid_) returning id into userid_;
	return userid_;
end;
$$ LANGUAGE plpgsql;
grant all on  function create_user(account_ text, password_ text, name_ text, description_ text, orgid_ integer) to csapp;

create or replace function create_admin_user(account_ text, password_ text, name_ text, description_ text, orgid_ integer) returns integer as $$
declare
userid_ integer;
begin
	insert into t_user2(account, password, name, orgid) values(account_, encode(digest(concat(password_, '{', account_, '}'), 'sha1'), 'hex'), name_, orgid_) returning id into userid_;
	insert into t_user2_modules(role, userid, moduleid) values('admin', userid_, 1); -- api/v2/account
	insert into t_user2_modules(role, userid, moduleid) values('admin', userid_, 9); -- api/v2/accadm
	return userid_;
end;
$$ LANGUAGE plpgsql;
grant all on  function create_admin_user(account_ text, password_ text, name_ text, description_ text, orgid_ integer) to csapp;

create or replace function change_password(account_ text, password_ text, orgid_ integer) returns integer as $$
begin
	update t_user2 set password= encode(digest(concat(password_, '{', account_, '}'), 'sha1'), 'hex') where account=account_ and orgid=orgid_ and account=account_;
	if not found then
		return 0;
	else
		return 1;
	end if;
end;
$$ LANGUAGE plpgsql;
grant all on  function change_password(account_ text,password_ text, orgid_ integer) to csapp;

create or replace function delete_organization(orgid_ integer) returns integer as $$
begin
	delete from t_org_modules where orgid = orgid_;
	delete from t_organization where id = orgid_;
	return 1;
end;
$$ LANGUAGE plpgsql;
grant all on function delete_organization(orgid_ integer) to csapp;

create or replace function new_area(orgid_ integer, parentid_ integer, name_ text, description_ text, 
	west_ double precision, north_ double precision, east_ double precision, south_ double precision, elevation_ double precision)
returns integer as $$
declare
areaid_ integer;
begin
	insert into t_area(orgid, parentid, name, description, west, north, east, south, elevation) 
	values(orgid_, parentid_, name_, description_, west_, north_, east_, south_, elevation_) returning id into areaid_;
	PERFORM update_parent_area_gps(parentid_);
	PERFORM update_org_gps(orgid_);
	return areaid_;
end;
$$ LANGUAGE plpgsql;

grant all on function new_area(integer, integer, text, text, double precision, double precision, double precision, double precision, double precision) to csapp;

create or replace function update_area(orgid_ integer, areaid_ integer, name_ text, description_ text, 
	west_ double precision, north_ double precision, east_ double precision, south_ double precision, elevation_ double precision)
returns integer as $$
declare
parentid_ integer;
begin
	update t_area set name=name_, description=description_, west=west_, north=north_, east=east_, south=south_, elevation=elevation_ 
	where orgid=orgid_ and id=areaid_
	returning parentid into parentid_;
	PERFORM update_parent_area_gps(parentid_);
	PERFORM update_org_gps(orgid_);
	return areaid_;
end;
$$ LANGUAGE plpgsql;

grant all on function update_area(integer, integer, text, text, double precision, double precision, double precision, double precision, double precision) to csapp;


create table t_application (
	id bigserial primary key,
	name varchar(128) not null unique,
	description varchar(256)
);
grant all on t_application to csapp;

create table t_area_euid (
	id bigserial primary key,
	areaid bigint not null references t_area(id),
	euid bigint not null references t_node_info2(id),
	orgid bigint not null references t_organization(id),
	appid bigint not null references t_application(id),
	unique (euid,appid)
);
grant all on t_area_euid  to csapp;
grant all on t_area_euid_id_seq  to csapp;

create or replace function new_application(name_ text, description_ text) returns integer as $$
declare
appid_ integer;
begin
	insert into t_application(name, description) values(name_, description_) returning id into appid_;
	return appid_;
end;
$$ LANGUAGE plpgsql;

grant all on function new_application(text, text) to csapp;


create table t_module_ui (
	id bigserial primary key,
	moduleid bigint references t_modules(id),
	name varchar(128) not null unique,
	displayname varchar(128) not null unique,
	description varchar(256)
);
grant all on t_module_ui to csapp;
grant all on t_module_ui_id_seq to csapp;

create or replace function new_uidef(modid_ integer, name_ text, displayname_ text, description_ text) returns integer as $$
declare
uiid_ integer;
begin
	insert into t_module_ui(moduleid, name, displayname, description) values(modid_, name_, displayname_, description_) returning id into uiid_;
	return uiid_;
end;
$$ LANGUAGE plpgsql;

grant all on function new_uidef(integer, text, text, text) to csapp;

create table t_sprinkler_data (
	id bigserial primary key,
	euid bigint not null references t_node_info2(id),
	time timestamp,
	pressure integer,
	pressure_trend integer,
	signal smallint,
	simid varchar(64)
);
create index idx_t_sprinkler_data_time on t_sprinkler_data(time);
grant all on t_sprinkler_data to csapp;
grant all on t_sprinkler_data_id_seq to csapp;

create or replace view v_area_euid_info as 
select t_area.id as areaid, t_area.parentid, t_area.name address,t_area.orgid,
t_area.west,
t_area.east,
t_area.north,
t_area.south,
t_area.elevation,
t_area_euid.euid,
t_area_euid.appid,
t_node_info2.euid seuid
from t_area, t_area_euid,t_node_info2
where t_area.id=t_area_euid.areaid  and t_area_euid.euid = t_node_info2.id;


create view v_latest_hydrant_data as
select a.euid as euid,
a."time",
b.pressure,
b.sim_id
from ( select t_hydrant_data.euid,
	max(t_hydrant_data."time") AS "time"
	from t_hydrant_data
	group by t_hydrant_data.euid) a,
t_hydrant_data b
where a.euid = b.euid AND a."time" = b."time";

create or replace view v_hydrant_status2 as 
select t1.*, t2."time", t2.pressure, t2.sim_id 
from  v_area_euid_info t1 
left join v_latest_hydrant_data t2 on t1.euid = t2.euid
where t1.appid=1;
grant all on v_hydrant_status2 to csapp;

create or replace view v_hydrant_history as
select t1.*, t2."time", t2.pressure, t2.sim_id
from  v_area_euid_info t1
left join t_hydrant_data t2 on t1.euid = t2.euid
where t1.appid=1;
grant all on v_hydrant_history to csapp;

create view v_latest_sprinkler_data as
select a.euid as euid,
a."time",
b.pressure,
b.simid as sim_id
from ( select t_sprinkler_data.euid,
	max(t_sprinkler_data."time") AS "time"
	from t_sprinkler_data
	group by t_sprinkler_data.euid) a,
t_sprinkler_data b
where a.euid = b.euid AND a."time" = b."time";

grant all on  v_latest_sprinkler_data to csapp;

create or replace view v_sprinkler_status as 
select t1.*, t2."time", t2.pressure, t2.sim_id 
from  v_area_euid_info t1 
left join v_latest_sprinkler_data t2 on t1.euid = t2.euid
where t1.appid=2;
grant all on  v_sprinkler_status to csapp;

create view v_sprinkler_history as
select t1.*, t2."time", t2.pressure, t2.simid
from  v_area_euid_info t1
left join t_sprinkler_data t2 on t1.euid = t2.euid
where t1.appid=2;
grant all on  v_sprinkler_history to csapp;

create or replace function new_device(seuid_ text, hwtype_ text, hwversion_ text, fwversion_ text, update_time_ timestamp, sim_id_ text, orgid_ integer) returns integer as $$
declare
euid_ integer;
begin
	    insert into t_node_info2(euid, hwtype, hwversion, fwversion, update_time, sim_id, orgid) 
		values(euid_, hwtype_, hwversion_, fwversion_, update_time_, sim_id_, orgid_) returning id into euid_;
		return euid_;
end;
$$ LANGUAGE plpgsql;

grant all on function new_device(seuid_ text, hwtype_ text, hwversion_ text, fwversion_ text, update_time_ timestamp, sim_id_ text, orgid_ integer) to csapp;

insert into t_application(name, description) values('magnetic','terrestrial  magnetic sensor');

create table t_magnetic_data (
	id bigserial primary key,
	euid bigint not null references t_node_info2(id),
	time timestamp,
	x_acceleration integer, 
	y_acceleration integer, 
	z_acceleration integer, 
	angle double precision,
	signal smallint,
	sim_id varchar(64)
);
create index idx_t_magnetic_data_time on t_magnetic_data(time);
grant all on t_magnetic_data to csapp;
grant all on t_magnetic_data_id_seq to csapp;

create view v_latest_magnetic_data as
select a.euid as euid,
a."time",
b.x_acceleration,
b.y_acceleration,
b.z_acceleration,
b.sim_id
from ( select t_magnetic_data.euid,
	max(t_magnetic_data."time") AS "time"
	from t_magnetic_data
	group by t_magnetic_data.euid) a,
t_magnetic_data b
where a.euid = b.euid AND a."time" = b."time";

grant all on  v_latest_magnetic_data to csapp;

create view v_magnetic_history as
select t1.*, t2."time", t2.x_acceleration, t2.y_acceleration, t2.z_acceleration, t2.sim_id
from  v_area_euid_info t1
left join t_magnetic_data t2 on t1.euid = t2.euid
where t1.appid=3;
grant all on  v_magnetic_history to csapp;

create or replace view v_magnetic_status as
select t1.*, t2."time", t2.x_acceleration, t2.y_acceleration, t2.z_acceleration, t2.sim_id
from  v_area_euid_info t1
left join v_latest_magnetic_data t2 on t1.euid = t2.euid
where t1.appid=3;
grant all on  v_magnetic_status to csapp;

create or replace function update_org_gps(orgid_ bigint) returns  void as $$
declare
begin
	update t_organization set (west,north,east,south)= (select max(west) west, max(north) north, max(east) east, max(south) south from t_area where orgid=orgid_ ) where id=orgid_ ;
end;
$$ LANGUAGE plpgsql;
grant all on function update_org_gps (orgid_ bigint) to csapp;

create or replace function update_parent_area_gps(areaid bigint) returns  void as $$
declare
parentid_ bigint := areaid;
begin
	PERFORM  id from t_area where parentid = parentid_ limit 1;
	if found then 
		while parentid_ is not null loop
			update t_area set (west,north,east,south)= (select max(west) west, max(north) north, max(east) east, max(south) south from t_area where parentid=parentid_) where id=parentid_ returning parentid into parentid_;
		end loop;
	end if;
end;
$$ LANGUAGE plpgsql;
grant all on function update_parent_area_gps(areaid bigint) to csapp;


create or replace function update_xhs_electric_node(new_batch boolean, euid_ text, channel_number_ integer,
loop_number_ integer, address_ integer, manufacturer_ integer, 
equipment_type_ integer, signal_type_ integer, minimum_range_ integer, 
maximum_range_ integer, unit_ text, marker_position_ integer, device_coding_ integer) 
returns  void as $$
declare
pk_of_euid integer;
guid text;
begin
	select id into pk_of_euid from t_node_info2 where euid = euid_;
    if found then
			if new_batch is true then 
				delete  from t_xhs_electric_node
				where euid = pk_of_euid and address = address_;
				select uuid_generate_v4()::text into guid;
			else
				select serial into guid from t_xhs_electric_node where euid = pk_of_euid and address = address_ limit 1;
			end if;

			insert into t_xhs_electric_node(euid, channel_number, loop_number, 
				address, manufacturer, equipment_type, signal_type, 
				minimum_range, maximum_range, unit, marker_position, device_coding, serial, time)
			values(pk_of_euid, channel_number_, loop_number_, 
				address_, manufacturer_, equipment_type_, signal_type_, 
				minimum_range_, maximum_range_, unit_, marker_position_, device_coding_, guid, localtimestamp);

			insert into t_xhs_electric_node_history(euid, channel_number, loop_number, 
				address, manufacturer, equipment_type, signal_type, 
				minimum_range, maximum_range, unit, marker_position, device_coding, serial, time)
			values(pk_of_euid, channel_number_, loop_number_, 
				address_, manufacturer_, equipment_type_, signal_type_, 
				minimum_range_, maximum_range_, unit_, marker_position_, device_coding_, guid, localtimestamp);
	end if;
end;
$$ LANGUAGE plpgsql;
grant all on function update_xhs_electric_node(boolean, euid_ text, channel_number_ integer,
loop_number_ integer, address_ integer, manufacturer_ integer, 
equipment_type_ integer, signal_type_ integer, minimum_range_ integer, 
maximum_range_ integer, unit_ text, marker_position_ integer, device_coding_ integer) to csapp;




create or replace function update_xhs_electric_data(new_batch boolean, euid_ text, 
signal_ integer, channel_number_ integer, analog_value_ double precision, status_ integer, address_ integer )
returns  void as $$
declare
pk_of_euid integer;
guid text;
begin
    select id into pk_of_euid from t_node_info2 where euid = euid_;
    if found then
		if new_batch is true then
			delete from t_xhs_electric_status 
			where euid = pk_of_euid and address = address_;
		end if;
		select serial into guid from t_xhs_electric_node where euid = pk_of_euid and address = address_ limit 1;

		insert into t_xhs_electric_data(euid, signal, channel_number, analog_value, status, address, serial, time)
		values(pk_of_euid, signal_, channel_number_, analog_value_, status_, address_, guid, localtimestamp);
		insert into t_xhs_electric_status(euid, signal, channel_number, analog_value, status, address, serial, time)
		values(pk_of_euid, signal_, channel_number_, analog_value_, status_, address_, guid, localtimestamp);
	end if;
end;
$$ LANGUAGE plpgsql;
grant all on function update_xhs_electric_data(new_batch boolean, euid_ text, signal_ integer, channel_number_ integer,
analog_value_ double precision, status_ integer, address_ integer) to csapp;



